from dao.iotdbhelper import Iot
from datetime import datetime
import pandas as pd
import time

iot = Iot()
device_ids = [
    "K_YSB5_D3",
    "K_YSB5_D2",
    "K_YSB5_D1",
    "K_YSB4_D3",
    "K_YSB4_D2",
    "K_YSB4_D1",
    "K_YSB3_X3",
    "K_YSB3_X2",
    "K_YSB3_X1",
    "K_YSB3_E3",
    "K_YSB3_E2",
    "K_YSB3_E1",
    "K_YSB3_C3",
    "K_YSB3_C2",
    "K_YSB3_C1",
    "K_YSB2_G3",
    "K_YSB2_G2",
    "K_YSB2_G1",
    "K_YSB1_D3",
    "K_YSB1_D2",
    "K_YSB1_D1"
]  # 多个设备ID列表
time_range_start = '2024-10-01 00:00:00'  # 你希望获取的时间范围开始时间
time_range_end = '2025-01-21 23:59:59'    # 你希望获取的时间范围结束时间

# 用于存储所有设备事件的列表
all_events = []

# 转换时间为时间戳
start_timestamp = int(time.mktime(datetime.strptime(time_range_start, '%Y-%m-%d %H:%M:%S').timetuple()) * 1000)
end_timestamp = int(time.mktime(datetime.strptime(time_range_end, '%Y-%m-%d %H:%M:%S').timetuple()) * 1000)

# 查询每个设备的数据
for device_id in device_ids:
    try:
        # 获取测点数据
        sql_query = f"SELECT Running FROM root.iotdata.L39t5xtgx.{device_id} WHERE time >= {start_timestamp} AND time <= {end_timestamp} order by time"
        result = iot.search(sql_query)

        if result.empty:
            print(f"设备 {device_id} 在指定时间范围内没有数据。")
            continue

        # 将结果转换为 pandas DataFrame
        df = pd.DataFrame(result)

        # 如果返回的数据中包含时间戳和状态信息，需要先提取
        df['timestamp'] = df['Time'].apply(lambda x: datetime.fromtimestamp(x / 1000))
        df['pump_status'] = df[f'root.iotdata.L39t5xtgx.{device_id}.Running']

        # 处理开关时间
        open_time = None
        for index, row in df.iterrows():
            if row['pump_status'] == 1 and open_time is None:  # 开始
                open_time = row['timestamp']
            elif row['pump_status'] == 0 and open_time is not None:  # 结束
                close_time = row['timestamp']
                duration = (close_time - open_time).total_seconds() / 60  # 计算持续时间（分钟）
                all_events.append({
                    "设备名": device_id,
                    "开启时间": open_time,
                    "关闭时间": close_time,
                    "持续时间/min": duration
                })
                open_time = None  # 重置 open_time

    except Exception as e:
        print(f"设备 {device_id} 数据处理失败：{e}")

# 将结果保存到 Excel
events_df = pd.DataFrame(all_events)
events_df.to_excel("open_time.xlsx", index=False)